﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class PhieuthanhtoanDAL : BaseDAL, UTL.IBaseDAL 
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @" SELECT *,(Case when a.TRANGTHAITHANHTOAN = 0 then N'Chưa đóng tiền' else N'Đã đóng tiền' end) as TTPTT FROM phieuthanhtoan a join phieukham b on a.sttphieu = b.sttphieu and a.ngaykham=b.ngaykham join nhanvien c on a.ma_nv=c.ma_nv";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM phieuthanhtoan WHERE maphieuthanhtoan = '{0}'";
            sql = string.Format(sql,id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (phieuthanhtoan)obj;
            string sql = @"set dateformat mdy; INSERT INTO phieuthanhtoan(maphieuthanhtoan,ngaylapphieu,tongtien,sttphieu,ngaykham,ma_nv,trangthaithanhtoan) VALUES('{0}','{1}',{2},'{3}','{4}','{5}','{6}') ";
            sql = string.Format(sql,o.maphieuthanhtoan,o.ngaylapphieu,o.tongtien,o.sttphieu,o.ngaykham,o.manv,o.trangthaiphieuthanhtoan);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (phieuthanhtoan)obj;
            string sql = @"set dateformat mdy; UPDATE phieuthanhtoan SET ngaylapphieu='{0}', tongtien={1}, sttphieu='{2}', ngaykham='{3}', ma_nv='{4}',trangthaithanhtoan='{5}' WHERE maphieuthanhtoan='{6}' ";
            sql = string.Format(sql,o.ngaylapphieu, o.tongtien, o.sttphieu, o.ngaykham, o.manv,o.trangthaiphieuthanhtoan,o.maphieuthanhtoan);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_phieukham1()
        {
            string sql = @" SELECT sttphieu as idstt, ngaykham as idngay FROM phieukham where trangthaithanhtoan=0";
            return ExecuteQuery(sql);
        }
        public DataTable Select_phieukham()
        {
            string sql = @" SELECT sttphieu as idstt, ngaykham as idngay FROM phieukham";
            return ExecuteQuery(sql);
        }
        public DataTable Select_nhanvien()
        {
            string sql = @" SELECT ma_nv as id, ten_nv as ten FROM nhanvien";
            return ExecuteQuery(sql);
        }

        /// <summary>
        /// lay ...
        /// </summary>
        /// <returns></returns>
        public string Select_max()
        {
            string sql = @"SELECT max(maphieuthanhtoan) as id FROM phieuthanhtoan";
            DataTable tb= ExecuteQuery(sql);
            
            return tb.Rows.Count>0? "0"+tb.Rows[0]["id"]:"0";
        }

        public string select_tongtiendoenthuoc(object obj)
        {
            var o = (phieuthanhtoan)obj;
            string sql = @"set dateformat mdy;select sum(c.SOLUONG*d.DONGIA*f.TYLEDONG)as tien from PHIEUKHAM a join DONTHUOC b on a.NGAYKHAM=b.NGAYKHAM and a.STTPHIEU=b.STTPHIEU join COTHUOC c on b.MADONTHUOC=c.MADONTHUOC join DONGIATHUOC d on c.MATHUOC=d.MATHUOC join BENHNHAN e on e.MA_BN=a.MA_BN join DOITUONG f on e.MADT=f.MADT where a.STTPHIEU='{0}' and a.NGAYKHAM='{1}'";
            sql = string.Format(sql, o.sttphieu, o.ngaykham);

            DataTable dt = ExecuteQuery(sql);
            return dt.Rows[0]["tien"] + "";
        }

        public bool deleteobj(object obj)
        {
            throw new NotImplementedException();
        }
        
        public void update_phieukham(object obj)
        {
            var o = (phieuthanhtoan)obj;
            string sql = @"set dateformat mdy; UPDATE phieukham SET trangthaithanhtoan='{0}' WHERE sttphieu='{1}' and ngaykham='{2}' ";
            sql = string.Format(sql, o.trangthaiphieukham, o.sttphieu, o.ngaykham);
            ExecuteNonQuery(sql);
        }

        public DataTable Select_Report(string _maphieutt)
        {
            string sql = @" SELECT MAPHIEUTHANHTOAN as MaPTT, NGAYLAPPHIEU as Ngaylap, Tongtien, STTPhieu, Ngaykham, b.ten_nv as hoten, trangthaithanhtoan as trangthai
                            FROM Phieuthanhtoan a join Nhanvien b on a.Ma_NV = b.Ma_NV
                            WHERE MAPHIEUTHANHTOAN = '{0}' ";
            sql = string.Format(sql,_maphieutt);
            return ExecuteQuery(sql);
        }

        public DataTable Select_sttphieu(string stt)
        {
            string sql = @" SELECT Ngaykham as idngay
                            FROM PHIEUKHAM
                            WHERE STTPHIEU = '{0}' and trangthaithanhtoan=0";
            sql = string.Format(sql,stt);
            return ExecuteQuery(sql);
        }
    }
}
